package com.flong.modules.service;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.flong.modules.AppException;
import com.flong.modules.BaseService;
import com.flong.modules.model.oracle.UserTabColumns;
import com.flong.modules.utils.DBHelper;
import com.flong.modules.utils.ObjectUtil;
import com.flong.modules.utils.PropertiesHelper;

/**
 * 
 * @Description	表属性
 * @ClassName	TableColumnsService
 * @Date		2017年2月23日 下午2:36:18
 * @Author		liangjilong
 * @Copyright (c) All Right Reserved , 2017.
 */
@SuppressWarnings("all")
public class TableColumnsService extends BaseService{
	
	/**
	 * @Description 获取所有列
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午2:49:12
	 * @param table
	 * @return
	 * @throws Exception 		参数
	 * @return 		List<Map<String,Object>> 返回类型
	 */
	public static List<Map<String, Object>> getCols(String table) throws Exception {
		List<Map<String, Object>> cols = new ArrayList<Map<String,Object>>();
		ResultSetMetaData md = DBHelper.query("select * from " + table + " where 1 = 2 ", null).getMetaData();
		 for (int i = 1; i <= md.getColumnCount(); i++) {
			 Map<String, Object> col = new HashMap<String, Object>();
			 cols.add(col);
			 col.put(COLS_NAME, md.getColumnName(i));
			 col.put(COLS_CLASS, md.getColumnClassName(i));
			 col.put(COLS_SIZE, md.getColumnDisplaySize(i));
			 
			 String _type = null;
			 String type = md.getColumnTypeName(i);
			 if(type.equals("INT")) {
				 _type = "INTEGER";
			 } else if(type.equals("DATETIME")) {
				 _type = "TIMESTAMP";
			 } else {
				 _type = type;
			 }
			 col.put(COLS_TYPE, _type);
		}
		return cols;
	}
	
	
	/**
	 * @Description 根据表和列进行获取条数
	 * @Author		liangjilong
	 * @Date		2017年2月23日 下午3:47:24
	 * @param tableName
	 * @param column
	 * @return
	 * @throws Exception 		参数
	 * @return 		int 返回类型
	 */
	public static int getCountTabColumns(String tableName,String column) throws Exception{
		int result = -1;
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		String dialect = PropertiesHelper.get("jdbc.dialect");//获取方言
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql = " select count(*) num from user_tab_columns where lower(table_name)=lower('"+tableName+"') and lower(column_name)=lower('"+column+"')";//
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
			//sql = " select * from user_tables ";//SQLSERVER是用+链接
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
			//sql = " select count(*) as num from information_schema.tables where upper(table_schema)=concat('',upper('"+tableName+"'),'') order by table_name ";
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		return getNumFieldValue(result, sql);
	}
	
	
	/**
	 * 
	 * @Description 根据表名获取系统所有表信息
	 * @Author		liangjilong
	 * @Date		2017年3月1日 上午9:28:49
	 * @param tableName
	 * @return
	 * @throws Exception 		参数
	 * @return 		UserTabColumns 返回类型
	 */
	public static List<UserTabColumns> getUserTabColumns(String tableName)throws Exception{
		StringBuffer sqlBuffer = new StringBuffer(); 
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		String dialect = PropertiesHelper.get("jdbc.dialect");//获取方言
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql= getUserTabColumnsOracleSQL()+" from user_tab_columns where upper(table_name)=upper('"+tableName+"')";
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		psmt = conn.prepareStatement(sql);
		rs = psmt.executeQuery();
		List<UserTabColumns> userTabColumns = DBHelper.convertToEntitys(rs, UserTabColumns.class);
		return userTabColumns;
	}
	
	
	/**
	 * @Description 判断表的列名约束是否为null和not null(N不为空,Y为空)
	 * @Author		liangjilong
	 * @Date		2017年3月1日 上午9:44:30
	 * @param tableName
	 * @param columnName
	 * @return
	 * @throws Exception 		参数
	 * @return 		boolean 返回类型
	 */
	public static boolean isNullColumnsNameConstraint(String tableName,String columnName)throws Exception{
		StringBuffer sqlBuffer = new StringBuffer(); 
		conn = DBHelper.getInstance().getConnection(); //链接数据库
		String dialect = PropertiesHelper.get("jdbc.dialect");//获取方言
		if(ObjectUtil.isNotEmpty(dialect) && "ORACLE".equalsIgnoreCase(dialect)){
			sql= getUserTabColumnsOracleSQL()+" from user_tab_columns where upper(table_name)=upper('"+tableName+"') and upper(column_name)=upper('"+columnName+"')";
		}else if(ObjectUtil.isNotEmpty(dialect) && "SQLSERVER".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else if(ObjectUtil.isNotEmpty(dialect) && "MYSQL".equalsIgnoreCase(dialect)){
			//@Dcript TODO 
		}else{
			throw new AppException(1,"方言配置有误，目前只支持MYSQL和Oracle");
		}
		psmt = conn.prepareStatement(sql);
		rs = psmt.executeQuery();
		List<UserTabColumns> userTabColumnList = DBHelper.convertToEntitys(rs, UserTabColumns.class);
		if(ObjectUtil.isNotEmpty(userTabColumnList)){
			UserTabColumns userTabColumns = userTabColumnList.get(0);
			String nullable = userTabColumns.getNullable();
			if("N".equalsIgnoreCase(nullable)){
				DBHelper.release(conn, psmt, rs);
				return false;
			}else{
				return true;
			}
		}else{
			return false;
		}
	}
	/**
	 * @Description 获取Oracle的user_tab_column查询SQL
	 * @Author		liangjilong
	 * @Date		2017年3月1日 上午9:32:32
	 * @return 		参数
	 * @return 		String 返回类型
	 */
	private static String getUserTabColumnsOracleSQL(){
		StringBuffer sqlBuffer = new StringBuffer(); 
		sqlBuffer.append("select table_name, column_name, data_type, data_type_mod, data_type_owner, data_length,");
		sqlBuffer.append("data_precision, data_scale, nullable, column_id,default_length, data_default, num_distinct,");
		sqlBuffer.append("low_value, high_value,character_set_name, char_col_decl_length,global_stats, user_stats, ");
		sqlBuffer.append("avg_col_len, char_length, char_used,v80_fmt_image, data_upgraded, histogram ").append(" ");
		return sqlBuffer.toString();
	}
	
	/**
	 * 获取字段
	 * @param name
	 * @return
	 */
	private String getFieldName(String name) {
		String _name = getClassName(name);
		return _name.substring(0, 1).toLowerCase() + _name.substring(1);
	}
	
	/***
	 * 获取类的
	 * @param name
	 * @return
	 */
	private String getClassName(String name) {
		String[] names = name.split("_");
		StringBuilder sb = new StringBuilder();
		for (String n : names) {
			if(n.length() == 0) {
				sb.append("_");
			} else {
				sb.append(n.substring(0, 1).toUpperCase());
				if(n.length() > 1) {
					sb.append(n.substring(1).toLowerCase());
				}
			}
		}
		return sb.toString();
	}
	
	
	
}
